﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading;

/*
 * SqLite Handle
 * 
 * Parameter SQLiteConnectionStringBuilder zur übergabe an die Connection
 *      conBuilder.DataSource = ".";
 *      conBuilder.InitialCatalog = "Northwind"; 
 *      conBuilder.UserID = "Testuser";
 *      conBuilder.Password = "26gf28"; 
 * 
 * Parameter string Connectionstring
 * this.connectionString = "Data Source=" + source + ";UseUTF16Encoding=False;Synchronous=Normal; Version=3;New=False";
 *
 */

namespace MagicCardManager
{
    class SqLiteHandle
    {
        //////////////////////////////////////////////////////////////////////////////////
        // Attribute
        #region ATTRIBUTE
        private ManualResetEvent[] doneEvent;

        /// <summary>
        /// DataSet für interne Datenverarbeitung
        /// </summary>
        /// 
        private dsDb m_Data = null;
        private SQLiteConnection connection = null;
        private SQLiteConnectionStringBuilder conBuilder = null;

        /// <summary>
        /// Letzte benutzte Id
        /// </summary>
        private int identity = 0;
        #endregion ATTRIBUTE

        //////////////////////////////////////////////////////////////////////////////////
        // Delegate
        #region DELEGATE
        public delegate void UpdateDataeventhandler(object sender, SQLiteDataEventArgs e);
        public event UpdateDataeventhandler UpdateData;
        #endregion DELEGATE

        //////////////////////////////////////////////////////////////////////////////////
        // Constructor
        #region Constructor
        /// <summary>
        /// Defaultconstructor
        /// </summary>
        public SqLiteHandle()
        {

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="source"></param>
        public SqLiteHandle(string source)
        {
            conBuilder = new SQLiteConnectionStringBuilder();
            conBuilder.DataSource = source;

            doneEvent = new ManualResetEvent[20];

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="builder"></param>
        public SqLiteHandle(SQLiteConnectionStringBuilder builder)
        {
            conBuilder = builder;
            doneEvent = new ManualResetEvent[20];
        }
        #endregion Constructor

        //////////////////////////////////////////////////////////////////////////////////
        // Events
        #region EVENTS
        protected void OnUpdateData()
        {
            //UpdateData(this, new SQLiteDataEventArgs(m_Data));
        }
        #endregion EVENTS


        // Wrapper method for use with thread pool.
        public void ThreadPoolCallback_FillCardGrid(Object threadContext)
        {
           // int threadIndex = (int)threadContext;
            //doneEvent[threadIndex];

            FillCardGrid();
            //OnUpdateData();

            //doneEvent.Set();
        }


        
        //////////////////////////////////////////////////////////////////////////////////
        // Methodes
        public dsDb FillCardGrid()
        {
            using (connection = new SQLiteConnection(conBuilder.ConnectionString))
            {
                connection.Open();

                StringBuilder sb = new StringBuilder();
                sb.AppendLine(
                "SELECT card.card_id AS card_id, card.name AS name, card.artist AS artist, card.anzahl AS anzahl, card.amount AS amount, card.p_t AS p_t, " +
                    "b.rarity AS rarity, c.typ_main AS typ_main " +
                "FROM card AS card " +
                "INNER JOIN rarity_list AS b on card.rarity_list_id = b.rarity_list_id " +
                "INNER JOIN typ_main_list AS c on card.typ_main_list_id = c.typ_main_list_id;");

                using (SQLiteCommand cmd = new SQLiteCommand(sb.ToString(), connection))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                        SQLiteDataAdapter Adapter = new SQLiteDataAdapter(cmd);
                        if (m_Data == null) m_Data = new dsDb();
                        Adapter.Fill(m_Data, "cardGrid");
                        cmd.Dispose();
                    }
                    catch (SQLiteException ex)
                    {

                        int h = 0;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            //doneEvent.Set();
            //OnUpdateData();
            return (m_Data);
        }

        public dsDb UpdateCardGrid(DataRow dc)
        {
            using (connection = new SQLiteConnection(conBuilder.ConnectionString))
            {
                connection.Open();
                
                StringBuilder sb = new StringBuilder();
                sb.AppendLine(
                "SELECT card.card_id AS card_id, card.name AS name, card.artist AS artist, card.anzahl AS anzahl, card.amount AS amount, card.p_t AS p_t, " +
                    "b.rarity AS rarity, c.typ_main AS typ_main " +
                "FROM card AS card " +
                "INNER JOIN rarity_list AS b on card.rarity_list_id = b.rarity_list_id " +
                "INNER JOIN typ_main_list AS c on card.typ_main_list_id = c.typ_main_list_id;");

                using (SQLiteCommand cmd = new SQLiteCommand(sb.ToString(), connection))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                        SQLiteDataAdapter Adapter = new SQLiteDataAdapter(cmd);
                        if (m_Data == null) m_Data = new dsDb();
                        Adapter.Fill(m_Data, "cardGrid");
                        cmd.Dispose();
                    }
                    catch (SQLiteException ex)
                    {

                        int h = 0;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            //doneEvent.Set();
            //OnUpdateData();
            return (m_Data);
        }

        public dsDb Search_tbl_CardGrid(string k, string v)
        {
            using (connection = new SQLiteConnection(conBuilder.ConnectionString))
            {
                connection.Open();

                StringBuilder sb = new StringBuilder();
                sb.AppendLine(
                "SELECT card.card_id AS card_id, card.name AS name, card.artist AS artist, card.anzahl AS anzahl, card.amount AS amount, card.p_t AS p_t, " +
                    "b.rarity AS rarity, c.typ_main AS typ_main " +
                "FROM card AS card " +
                "INNER JOIN rarity_list AS b on card.rarity_list_id = b.rarity_list_id " +
                "INNER JOIN typ_main_list AS c on card.typ_main_list_id = c.typ_main_list_id " +
                "WHERE " + k + " LIKE '" + v + "%';");

                using (SQLiteCommand cmd = new SQLiteCommand(sb.ToString(), connection))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                        SQLiteDataAdapter Adapter = new SQLiteDataAdapter(cmd);

                        m_Data = null;
                        if (m_Data == null) m_Data = new dsDb();
                        Adapter.Fill(m_Data, "cardGrid");
                        cmd.Dispose();
                    }
                    catch (SQLiteException ex)
                    {

                        int h = 0;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            //doneEvent.Set();
            //OnUpdateData();
            return (m_Data);
        }

        public void SearchCard(SQLiteSearchDataContainer args)
        {
            using (connection = new SQLiteConnection(conBuilder.ConnectionString))
            {
                connection.Open();

                StringBuilder sb = new StringBuilder();
                sb.AppendLine(
                "SELECT card.card_id AS card_id, card.name AS name, card.artist AS artist, card.anzahl AS anzahl, card.amount AS amount, card.p_t AS p_t, " +
                    "b.rarity AS rarity, c.typ_main AS typ_main " +
                "FROM card AS card " +
                "INNER JOIN rarity_list AS b on card.rarity_list_id = b.rarity_list_id " +
                "INNER JOIN typ_main_list AS c on card.typ_main_list_id = c.typ_main_list_id " +
                "WHERE @key LIKE @value;");

                using (SQLiteCommand cmd = new SQLiteCommand(sb.ToString(), connection))
                {
                    try
                    {
                        cmd.Parameters.AddWithValue("key", args.Key);
                        cmd.Parameters.AddWithValue("value", args.Value);


                        cmd.ExecuteNonQuery();
                        SQLiteDataAdapter Adapter = new SQLiteDataAdapter(cmd);
                        if (m_Data == null) m_Data = new dsDb();
                        Adapter.Fill(m_Data, "cardGrid");
                        cmd.Dispose();
                    }
                    catch (SQLiteException ex)
                    {

                        int h = 0;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            OnUpdateData();
            //doneEvent.Set();

        }



        public int AddEmployee(string lastName, string firstName, double sallary, DateTime birthday)
        {
            using (connection = new SQLiteConnection(conBuilder.ConnectionString))
            {
                connection.Open();

                StringBuilder sb = new StringBuilder();
                sb.AppendLine("INSERT INTO [Employees] ([LastName], [FirstName], [Sallary], [Birthday])");
                sb.AppendLine("OUTPUT INSERTED.ID");
                sb.AppendLine("VALUES (@LastName, @FirstName, @Sallary, @Birthday)");

                using (SQLiteCommand cmd = new SQLiteCommand(sb.ToString(), connection))
                {
                    cmd.Parameters.AddWithValue("LastName", lastName);
                    cmd.Parameters.AddWithValue("FirstName", firstName ?? Convert.DBNull);
                    cmd.Parameters.AddWithValue("Sallary", sallary);
                    cmd.Parameters.AddWithValue("Birthday", birthday);

                    return cmd.ExecuteNonQuery();
                }
            }
        }



        public void AddCard(DataSet ds)
        {
            //// For each table in the DataSet, print the row values.
            //foreach(DataTable table in dataSet.Tables)
            //{
            //    foreach(DataRow row in ds.Tables["card"].Rows[0])
            //    {
            //        foreach (DataColumn column in table.Columns)
            //        {
            //            Console.WriteLine(row[column]);
            //        }
            //    }
            //}


            using (connection = new SQLiteConnection(conBuilder.ConnectionString))
            {
                connection.Open();

                StringBuilder sb = new StringBuilder();
                sb.AppendLine("INSERT INTO [card] ([card_id], [name], [anzahl], [amount], [artist], [rarity_list_id], [typ_main_list_id])");
                //sb.AppendLine("OUTPUT INSERTED.ID");
                sb.AppendLine("VALUES (NULL, @name, @anzahl, @amount, @artist, @rarity_list_id, @typ_main_list_id)");

                using (SQLiteCommand cmd = new SQLiteCommand(sb.ToString(), connection))
                {
                    cmd.Parameters.AddWithValue("name", ds.Tables["card"].Rows[0]["name"]);
                    cmd.Parameters.AddWithValue("artist", ds.Tables["card"].Rows[0]["artist"]);
                    cmd.Parameters.AddWithValue("anzahl", ds.Tables["card"].Rows[0]["anzahl"]);
                    cmd.Parameters.AddWithValue("amount", ds.Tables["card"].Rows[0]["amount"]);
                    cmd.Parameters.AddWithValue("rarity_list_id", ds.Tables["card"].Rows[0]["rarity_list_id"]);
                    cmd.Parameters.AddWithValue("typ_main_list_id", ds.Tables["card"].Rows[0]["typ_main_list_id"]);

                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }

                // Die letzt genutzte id zurückgeben
                sb = null;
                sb = new StringBuilder();
                sb.AppendLine("SELECT last_insert_rowid();");

                using (SQLiteCommand cmd = new SQLiteCommand(sb.ToString(), connection))
                {
                    object o = cmd.ExecuteScalar();
                    this.identity = Convert.ToInt32(o);
                    cmd.Dispose();
                }

                sb = null;
                sb = new StringBuilder();
                sb.AppendLine("INSERT INTO [edition] ([edition_id], [card_id], [anzKarten], [edition_list_id])");
                sb.AppendLine("VALUES (NULL, @card_id, @anzKarten, @edition_list_id)");

                using (SQLiteCommand cmd = new SQLiteCommand(sb.ToString(), connection))
                {
                    foreach (DataRow row in ds.Tables["edition"].Rows)
                    {
                        cmd.Parameters.AddWithValue("card_id", this.identity);
                        cmd.Parameters.AddWithValue("anzKarten", row["anzKarten"]);
                        cmd.Parameters.AddWithValue("edition_list_id", row["edition_list_id"]);
                        cmd.ExecuteNonQuery();
                    }
                    cmd.Dispose();
                }

                sb = null;
                sb = new StringBuilder();
                sb.AppendLine("INSERT INTO [color] ([color_id], [card_id], [color_list_id])");
                sb.AppendLine("VALUES (NULL, @card_id, @color_list_id)");

                using (SQLiteCommand cmd = new SQLiteCommand(sb.ToString(), connection))
                {
                    foreach (DataRow row in ds.Tables["edition"].Rows)
                    {
                        cmd.Parameters.AddWithValue("card_id", this.identity);
                        cmd.Parameters.AddWithValue("color_list_id", row["color_list_id"]);
                        cmd.ExecuteNonQuery();
                    }
                    cmd.Dispose();
                }
            }
        }




        public void AddSet(string name, int year)
        {
            using (connection = new SQLiteConnection(conBuilder.ConnectionString))
            {
                connection.Open();

                StringBuilder sb = new StringBuilder();
                sb.AppendLine("INSERT INTO [set_list] ([name], [year])");
                //sb.AppendLine("OUTPUT INSERTED.ID");
                sb.AppendLine("VALUES (@name, @year)");

                using (SQLiteCommand cmd = new SQLiteCommand(sb.ToString(), connection))
                {
                    cmd.Parameters.AddWithValue("name", name);
                    cmd.Parameters.AddWithValue("year", year);

                    try
                    {
                        cmd.ExecuteNonQuery();
                        connection.Close();
                    }
                    catch (SQLiteException ex)
                    {
                        int h = 0;
                    }
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public dsDb GetColorList()
        {
            int gfg = 0;
            //if (connection.State == ""
            using (connection = new SQLiteConnection(conBuilder.ConnectionString))
            {
                connection.Open();

                StringBuilder sb = new StringBuilder();
                sb.AppendLine("SELECT * FROM color_list");

                using (SQLiteCommand cmd = new SQLiteCommand(sb.ToString(), connection))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();

                        SQLiteDataAdapter Adapter = new SQLiteDataAdapter(cmd);

                        if (m_Data == null)
                            m_Data = new dsDb();
                        Adapter.Fill(m_Data, "color_list");
                        cmd.Dispose();
                    }
                    catch (SQLiteException ex)
                    {
                        int h = 0;
                    }
                    finally
                    {
                        connection.Close();
                    }            
                }
            }
            return (m_Data);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public dsDb GetTypMainList()
        {
            int gfg = 0;
            //if (connection.State == ""
            using (connection = new SQLiteConnection(conBuilder.ConnectionString))
            {
                connection.Open();

                StringBuilder sb = new StringBuilder();
                sb.AppendLine("SELECT * FROM typ_main_list");
                SQLiteFactory hhh = new SQLiteFactory();
                
                using (SQLiteCommand cmd = new SQLiteCommand(sb.ToString(), connection))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();

                        SQLiteDataAdapter Adapter = new SQLiteDataAdapter(cmd);

                        if (m_Data == null)
                            m_Data = new dsDb();
                        Adapter.Fill(m_Data, "typ_main_list");
                        cmd.Dispose();
                    }
                    catch (SQLiteException ex)
                    {
                        int h = 0;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return (m_Data);
        }



        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public dsDb GetRarityList()
        {
            int gfg = 0;
            //if (connection.State == ""
            using (connection = new SQLiteConnection(conBuilder.ConnectionString))
            {
                connection.Open();

                StringBuilder sb = new StringBuilder();
                sb.AppendLine("SELECT * FROM rarity_list");

                using (SQLiteCommand cmd = new SQLiteCommand(sb.ToString(), connection))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();

                        SQLiteDataAdapter Adapter = new SQLiteDataAdapter(cmd);

                        if (m_Data == null)
                            m_Data = new dsDb();
                        Adapter.Fill(m_Data, "rarity_list");
                        cmd.Dispose();
                    }
                    catch (SQLiteException ex)
                    {
                        int h = 0;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return (m_Data);
        }



        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public dsDb GetEditionListList()
        {
            using (connection = new SQLiteConnection(conBuilder.ConnectionString))
            {
                connection.Open();

                StringBuilder sb = new StringBuilder();
                sb.AppendLine("SELECT * FROM edition_list");

                using (SQLiteCommand cmd = new SQLiteCommand(sb.ToString(), connection))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();

                        SQLiteDataAdapter Adapter = new SQLiteDataAdapter(cmd);
                        if (m_Data == null)
                            m_Data = new dsDb();
                        Adapter.Fill(m_Data, "edition_list");
                        cmd.Dispose();
                    }
                    catch (SQLiteException ex)
                    {
                        int h = 0;
                        throw ex;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return (m_Data);
        }
    }

    #region EVENTARGS
    /// <summary>
    /// EventArgs für SQLite Änderungen
    /// </summary>
    public class SQLiteDataEventArgs : EventArgs
    {
        private object dbDataSet;

        public SQLiteDataEventArgs(object dbDS)
        {
            dbDataSet = dbDS;
        }

        public object DBDataSet
        {
            get
            {
                return (dbDataSet);
            }
        }
    }
    #endregion EVENTARGS

    

    #region DATAOBJECT
    public class SQLiteSearchDataContainer
    {
        private object m_key;
        private object m_value;

        public object Key
        {
            get
            {
                return (m_key);
            }
        }

        public object Value
        {
            get
            {
                return (m_value);
            }
        }
                
        public SQLiteSearchDataContainer Data
        {
            get
            {
                return (this);
            }
        }

        public SQLiteSearchDataContainer(object key, object val)
        {
            m_key = key;
            m_value = val;
        }
    }
    #endregion EVENTADATAOBJECTRGS
}
